# -*- coding:utf-8 -*-
from bs4 import BeautifulSoup
import requests
import re
import MySQLdb
import requests
import json
import time
import xlwt
# 解决出现的写入错误
import sys
import importlib
importlib.reload(sys)

#台风轨迹点
start_time = [] #台风到达时间
lon = [] #台风到达地经度
lat = [] #台风到达地纬度
central_pressure = [] #台风中心气压
wind = [] #台风风速风力（米/秒）
direction = [] #未来移向
feature_speed = [] #未来移速
number_new=[] #台风编号
#台风登陆时的轨迹点
start_time_1 = [] #台风到达时间
lon_1 = [] #台风到达地经度
lat_1 = [] #台风到达地纬度
central_pressure_1 = [] #台风中心气压
wind_1 = [] #台风风速风力（米/秒）
direction_1 = [] #未来移向
feature_speed_1 = [] #未来移速
denglu = [] #台风登陆描述
number_newDL=[] #台风编号


#year = input("输入台风的年份") # 输入台风的年份
Year=[2018,2019]
#number = input("输入台风的编号") # 输入台风的编号
Number=[]
Number18=[1801,1802,1803,1804,1805,1806,1807,1808,1809,1810,1811,1812,1813,1814,1815,1816,1817,1818,1819,1820,1821,1822,1823,1824,1825,1826,1827,1828,1829] #,1800018,1800019
Number19=[1901,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911,1912,1913,1914,190003,190003,190005]

#连接数据库
print('连接到mysql服务器...')
conn = MySQLdb.connect(host='127.0.0.1', port = 3306, user='root', passwd='123456', db='test', charset='utf8')
print('连接上了!')
cur = conn.cursor()

for index in range(len(Year)):
    year=str(Year[index])
    if year=="2018":
        Number=Number18
    else :
        Number = Number19
    for index in range(len(Number)):
        number=str(Number[index])
    #Python爬虫天气预报（小白入门） | Coder·码农网  https://www.codercto.com/a/9087.html
    #含时间戳的url，请求头
        url = "http://d1.weather.com.cn/typhoon/typhoon_data/"+year+"/"+number+".json?callback=getData&_="+str(int(round((time.time()*1000))))
        headers = {
                "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36",
                "Referer": "http://typhoon.weather.com.cn/gis/typhoon_p.shtml",
            }
        r = requests.get(url,headers=headers)
        print(r.text)
        a = json.loads(r.text[8:-1])#解析json文件
        print(a["typhoon"][8])
        print(len(a["typhoon"][8]))


        #print(a["typhoon"][8][21])
        for i in range(len(a["typhoon"][8])):
            #b = a["typhoon"][8][i]
            #print(b[1],b[4],b[5],b[6],b[7],b[8],b[9])
            start_time.append(a["typhoon"][8][i][1])
            lon.append(a["typhoon"][8][i][4])
            lat.append(a["typhoon"][8][i][5])
            central_pressure.append(a["typhoon"][8][i][6])
            wind.append(a["typhoon"][8][i][7])
            direction.append(a["typhoon"][8][i][8])
            feature_speed.append(a["typhoon"][8][i][9])
            number_new.append(number)
            if len(a["typhoon"][8][i]) > 13:
                start_time_1.append(a["typhoon"][8][i][1])
                lon_1.append(a["typhoon"][8][i][4])
                lat_1.append(a["typhoon"][8][i][5])
                central_pressure_1.append(a["typhoon"][8][i][6])
                wind_1.append(a["typhoon"][8][i][7])
                direction_1.append(a["typhoon"][8][i][8])
                feature_speed_1.append(a["typhoon"][8][i][9])
                denglu.append(a["typhoon"][8][i][13])
                number_newDL.append(number)
                print(a["typhoon"][8][i][13])
        # 写入数据库

        j=0
        q=0
        for start_time1,lon1,lat1,central_pressure1,wind1,direction1,feature_speed1,number_new1\
             in zip(start_time,lon,lat,central_pressure,wind,direction,feature_speed,number_new):
            into = "INSERT INTO tf_geo(tf_geo_number,tf_geo_time,tf_geo_lon,tf_geo_lat,tf_geo_central_pressure,tf_geo_wind,tf_geo_direction,tf_geo_feature_speed) VALUES (%s,%s, %s,%s, %s,%s, %s,%s)"
            values = (number_new1,start_time1,lon1,lat1,central_pressure1,wind1,direction1,feature_speed1)
            cur.execute(into, values)
            conn.commit()
            j = j+1
        for start_time1,lon1,lat1,central_pressure1,wind1,direction1,feature_speed1,denglu1,number_newDL1\
             in zip(start_time_1,lon_1,lat_1,central_pressure_1,wind_1,direction_1,feature_speed_1,denglu,number_newDL):
            into = "INSERT INTO tf_loacl(tf_loacl_number,tf_loacl_time,tf_loacl_lon,tf_loacl_lat,tf_loacl_text) VALUES (%s,%s,%s, %s,%s)"
            values = (number_newDL1,start_time1,lon1,lat1,denglu1)
            cur.execute(into, values)
            conn.commit()
            q = q+1

# # 判断表是否存在，若存在则删除此表
# cur.execute("DROP TABLE xiaoshuo")
#
# # 创建表
# # cursor()： 使用该链接创建并返回的游标
# # execute()：执行一个数据库查询和命令
# # commit()：提交但前事物（写入数据时也会用到
# sql = """CREATE TABLE xiaoshuo(
#                  title  CHAR(20),
#                  sec_title  CHAR(20),
#                  content  VARCHAR(6499))"""
# cur.execute(sql)
# conn.commit()
# print('表创建成功!')

conn.close() #关闭数据库
